CREATE OR REPLACE FUNCTION "pipeline"."statistics_anomaly"("source_table" text, "out_table" varchar, "id_col" varchar, "feature_col" varchar)
  RETURNS "pg_catalog"."text" AS $BODY$
  import numpy as np
  import json
  import time

  #初始化返回值
  result = {
    "status": 0,
    "error_msg": "success",
    "result": {
      "input_params": {
        "out_table": out_table,
        "source_table": source_table,
        "id_col": id_col,
        "feature_col": feature_col,
      },
      "output_params": []
    }
  }

  #读数据
  def get_data_sql(source_table, id_col, feature_col):  
    #sql_str = "select %s, %s from %s" %(id_col, feature_col, source_table)
    sql_str = "select * from %s" % source_table
    return sql_str
  
  #获取表格属性信息
  def getTableMetaInfo(table_name):
    tmps = table_name.split(".")
    if len(tmps) > 0:
      table_name = tmps[1]	  
    sql_str = "select column_name, data_type from information_schema.columns where table_name = '%s'" %(table_name)
    rs = plpy.execute(sql_str)
    meta = {}
    for line in rs:
      meta[line['column_name']] = line['data_type']
    return meta
  
  #创建输出表
  def saveOutTable(datas, out_table, colsName, meta):
    value_tpl = "({}, {})"
    stringType = ["text", "character varying", "varchar", "char", "date", "character", 
        "timestamp", "time", "timestamp_with_timezone", "longvarchar", "longnvarchar", "nvarchar", "nchar"]
    if meta.get(id_col) in ["text", "character varying", "varchar", "char", "date", "character", 
        "timestamp", "time", "timestamp_with_timezone", "longvarchar", "longnvarchar", "nvarchar", "nchar"]:
      value_tpl = "('{}', {})"
    #table_name = "%s_%s" %(out_table, int(time.time()))
    table_name = out_table
    sql_str = "CREATE TABLE %s (%s %s, label varchar);" %(table_name, id_col, meta.get(id_col))
    plpy.execute(sql_str)
    sql_str = "INSERT INTO %s (%s, label) VALUES" %(table_name, id_col)
    _values = []
    for item in datas:
      tmp = value_tpl.format(item[0], item[1])
      _values.append(tmp)
    sql_str += ",".join(_values)
    plpy.execute(sql_str)
    return {"out_table_name": table_name, "cols": [id_col, "label"]}

  def throwError(e):
    global result
    result["status"] = 500
    result["error_msg"] = str(e)

   #主程序
  def begin_alg(source_table, out_table, id_col, feature_col):
    global result
    sourceTable = source_table
    if source_table.startswith("create view") or source_table.startswith("CREATE VIEW"):
        try:
            rs = plpy.execute(source_table)
            sourceTable = source_table.split(" ")[2]
        except Exception as e:
            plpy.execute("DROP VIEW IF EXISTS {}".format(sourceTable))
            msg = "sq={}, errorMsg={}".format(source_table, str(e))
            result["status"] = 500
            result["error_msg"] = msg
            return json.dumps(result)
    if len(feature_col.split(",")) > 1:
      throwError("can not support multi-featureCol")
      return json.dumps(result)   
    sql_str = 'CREATE TABLE {} AS select a.*, (CASE WHEN a."{}" > b.avgval + 3 * b.stdval or a."{}" < b.avgval - 3 * b.stdval THEN concat(-1) ELSE concat(1) END) as label from (select stddev("{}") as stdval, avg("{}") as avgval from {}) as b, {} as a'.format(out_table, feature_col, feature_col, feature_col, feature_col, sourceTable, sourceTable)
    #sql_str = get_data_sql(source_table, id_col, feature_col)
    rv = None
    try:
      rv = plpy.execute(sql_str)
    except Exception as e:
      plpy.execute("DROP TABLE IF EXISTS {}".format(out_table))
      throwError(e)
      return json.dumps(result)
    datas = []
    meta = getTableMetaInfo(out_table)
    colNames = meta.keys()
    tmpTable = {
        "out_table_name": out_table,
        "output_cols": colNames
    }
    result["result"]["output_params"].append(tmpTable)
    return json.dumps(result)
    
  if __name__ == '__main__':
    global result
    ret = begin_alg(source_table, out_table, id_col, feature_col)
    return ret
$BODY$
  LANGUAGE plpythonu VOLATILE
  COST 100